package com.ssy.lingxi.report.dao.purchase;

import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseSubmitTenderStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 *  投标DAO
 * @author ds
 * @date 2021/6/3
 * @version 2.0.0
 */
@Service
public class SubmitTenderDAO {


    @Resource
    @Qualifier("purchaseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 投标-统计数量
     * @return
     */
    public List<PurchaseSubmitTenderStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" m.member_id memberId,m.member_role_id roleId,")
                .append(" sum(case when (m.submit_tender_out_status in (1,3) )then 1 else 0 end) toBeSignupCount,")
                .append(" sum(case when (m.submit_tender_out_status in (4,6) )then 1 else 0 end) toBeCommitQualificationCount,")
                .append(" sum(case when (m.submit_tender_out_status=7 and m.submit_tender_in_status in (5,6,8,10) )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (m.submit_tender_out_status=7 and m.submit_tender_in_status = 7)then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (m.submit_tender_out_status=7 and m.submit_tender_in_status = 9)then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (m.submit_tender_out_status=7 and m.submit_tender_in_status = 11)then 1 else 0 end) toBeCommitCount")
                .append(" FROM purchase_invite_tender_member m inner join purchase_invite_tender t on m.invite_tender_id=t.id ")
                .append(" group by m.member_id,m.member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseSubmitTenderStatisticsTallyVO.class));
    }


}
